



;;populate main screen with selection from second listbox
(defun load_lot_into_main_page ()
  (setf chosen-lot (listbox-get-selection lot2listbox)) ;;returns number(int) in list. e.g. Make list to test (setf myvalue (cons 4 nil))
  ;; User didn't select anything:
  (if (not chosen-lot) ;;NIL is equivalent to boolean false. [ if (not value == if (!value) ]
      (dotimes (x 1)
	(error-report "Please select a 2nd listbox item" 55531) ;;wrap multiple commands in dotimes loop
	(return-from load_lot_into_main_page))  
      )
  ;;get primary key (timestamp) from indexed selection of lotnumber in listbox.
  (setf timestampnum (execute-single *db* "select Timedateunix from lots_base where CustomerID = ?  LIMIT 1 OFFSET ?;" customeridnum (car chosen-lot)))
  ;;start populating data
  (setf *lotlistreturn* (car (execute-to-list *db* "select Formula, Quantity, Timedateunix, Notes, Lotnumber from lots_base where Timedateunix = ?" timestampnum))) ;;car as it returns a list in a list.
  ;;parse / enter templates_base into entries
  (setf (text *top_e_formula* ) (nth 0 *lotlistreturn*)) ;;ltk clos access text slot from widget
  (setf (text *top_e_quantity*) (nth 1 *lotlistreturn*))
  (setf (text *top_e_date*)     (get-readable-time (nth 2 *lotlistreturn*)))
  (setf (text *top_e_notes*)    (nth 3 *lotlistreturn*))
  (setf (text *top_e_lot*)      (nth 4 *lotlistreturn*))
  (setf *lotlistreturn* (execute-single *db* "select Customer from lots_customers where CustomerID = ?;" customeridnum))
  (setf (text *top_e_customer*) *lotlistreturn*)
  ;;parse / enter templates_materials into entries
  (setf *lotlistreturn* (car (execute-to-list *db* "select Lbs, Oz, Grams, Materials, Internal from lots_materials where Timedateunix = ?" timestampnum))) 
  ;;process for multiple rows / lists in list
  ;;get count of rows
  (setf  *sqlite_row_count* (execute-single *db* "select COUNT(Materials) from lots_materials where Timedateunix = ? ;" timestampnum))
  ;;go through #'s equal to count and populate based on count
  (loop for x from 1 to 6 do
    (setf *lotlistreturn* (car (execute-to-list *db*
						"select Lbs, Oz, Grams, Materials, Internal from lots_materials where Timedateunix = ? order by Lbs ASC, Oz ASC, Grams ASC LIMIT 1 OFFSET ?;"
					        timestampnum (- x 1)))) ;;sqlite row starts at 0
    ;;make symbol names
    (setf *MAT_LOOP_MAT*
	  (symbol-in-symbol *MAT_LOOP_MAT* "*ENTRY_MAT" (write-to-string x) "*")) ;;this concatenate like function will create the entry name
    (setf *MAT_LOOP_LBS*
	  (symbol-in-symbol *MAT_LOOP_LBS* "*ENTRY_LBS" (write-to-string x) "*"))
    (setf *MAT_LOOP_OZ*
	  (symbol-in-symbol *MAT_LOOP_OZ*  "*ENTRY_OZ"  (write-to-string x) "*")) 
    (setf *MAT_LOOP_GMS*
	  (symbol-in-symbol *MAT_LOOP_GMS* "*ENTRY_GMS" (write-to-string x) "*"))
    (setf *MAT_LOOP_INT*
	  (symbol-in-symbol *MAT_LOOP_INT* "*ENTRY_INT" (write-to-string x) "*"))
    ;;assign value to symbols
    (setf (text (get-symbol-in-symbol *MAT_LOOP_LBS*)) (nth 0 *lotlistreturn*))
    (setf (text (get-symbol-in-symbol *MAT_LOOP_OZ* )) (nth 1 *lotlistreturn*))
    (setf (text (get-symbol-in-symbol *MAT_LOOP_GMS*)) (nth 2 *lotlistreturn*))
    (setf (text (get-symbol-in-symbol *MAT_LOOP_MAT*)) (nth 3 *lotlistreturn*))
    (setf (text (get-symbol-in-symbol *MAT_LOOP_INT*)) (nth 4 *lotlistreturn*))
	)
  )


;;populate second listbox with selection from first
(defun get_lots_from_customer ()

  (defparameter month_or_year_flag 0);; 0 for month, 1 for year. parameter seems to always initialize.
  
  ;;index of selection from listbox (number)
  (setf chosen-lot (listbox-get-selection lotlistbox))
  (if (not chosen-lot) ;;didn't choose anything...
      (dotimes (x 1)
	(error-report "Please select a customer" 555312)
	(return-from get_lots_from_customer)) 
      )
  ;;(print chosen-lot)
  ;;get customer id from index of listbox
  (setf customeridnum  (execute-single *db* "SELECT CustomerID FROM lots_customers ORDER BY Customer LIMIT 1 OFFSET ?;" (car chosen-lot))) ;;Sqlite offset starts from 0 like listbox.
  ;;provide results for a year (and optionally month) range
  ;;calculate range based on given year:
  (if (string-equal (text entrybox_year) "")
      (dotimes (x 1)
	(error-report "Please select a year" 551113)
	(return-from get_lots_from_customer)) 
      )

  ;;clear current (2nd) listbox
  (listbox-clear (listbox lot2listbox)) ;;CLOS. lot2listbox has slots. Access listbox slot. See lisp cookbook for examples. Also see ltk mailing list: https://mailman.common-lisp.net/pipermail/ltk-user/2007-October/000246.html
  (if (string-equal (text entrybox_month) "") ;;test. If they want the whole year, they can blank out the month. if true, go to next
      (dotimes (x 1) (setf year_calculate (universal-to-unix-time (encode-universal-time 0 0 0 1 1 (parse-integer (text entrybox_year)))))(setf month_or_year_flag 1));;calculate for whole year, else
      (setf year_calculate (universal-to-unix-time (encode-universal-time 0 0 0 1 (parse-integer (text entrybox_month)) (parse-integer (text entrybox_year)))));;calculate for year and month
      )

  ;;when in doubt, printf, or e from debugger to execute cmd

  ;;query all lots by that customer (optionally: for given year, and or month)
  ;;this is similar to the first listbox.
  ;;because we have a unix timestamp, it's easy to set the range in one field. (i.e. if we had separate columns for month, and year, this query would require more)
  (if (= month_or_year_flag 1) 
      (setf *lotlistreturn* (execute-to-list *db* "SELECT Lotnumber FROM lots_base Where  
                                                        CustomerID = ?
                                                        AND (
                                                           Timedateunix BETWEEN ? AND ?);"
					     customeridnum year_calculate (+ year_calculate one_year_in_unixtime))) ;;whole year
      (setf *lotlistreturn* (execute-to-list *db* "SELECT Lotnumber FROM lots_base Where  
                                                        CustomerID = ?
                                                        AND (
                                                           Timedateunix BETWEEN ? AND ?);"
					     customeridnum year_calculate (+ year_calculate one_month_in_unixtime)));;else, just month
      )
  ;; e.g.  select * from lots_base where customer = "a3rdguy" and Timedateunix between 1609477200 and 1641034126;
  ;;add data to second listbox.
  (listbox-append lot2listbox *lotlistreturn*)
  (print "month flag is:")
  (print month_or_year_flag)
  )


;;load initial window
(defun loadlotdialog ()
  (setf *lotloadwin* (make-instance 'toplevel :width 500 :height 400
					      :padx 5 :pady 5
					      :title "Load Lot"))
  (setf lotpane (make-instance 'frame :master *lotloadwin*
				      :width 500 :height 400))
  (setf *customer-names* (execute-to-list *db* "select Customer from lots_customers order by Customer"))
  (setf lotlistbox (make-instance 'scrolled-listbox :master lotpane ))
  (configure (listbox lotlistbox) :height 40)
  (listbox-append lotlistbox *customer-names*)
  
  (grid lotpane 0 0)
  (grid lotlistbox 0 0  :sticky "nsew")

  ;;todo, increase size of toplevel window

  
  ;;select and cancel buttons
  (setf *select_customer* (make-instance 'button :text "Select Customer"
						 :command #'(lambda () (get_lots_from_customer))
						 :master lotpane))
  (grid *select_customer*  0 1  :sticky "n" :pady 5 :padx 5)
  (grid-columnconfigure lotpane 0 :weight 1)
  (grid-columnconfigure lotpane 1 :weight 1)
  (grid-columnconfigure lotpane 2 :weight 1)
  (grid-columnconfigure lotpane 3 :weight 1)
  (grid-rowconfigure lotpane 0 :weight 1)
  (grid-rowconfigure lotpane 1 :weight 1)
  (grid-rowconfigure lotpane 2 :weight 1)
  (grid-rowconfigure lotpane 3 :weight 1)
  (grid-rowconfigure lotpane 4 :weight 1)
  (grid-rowconfigure lotpane 10 :weight 1)

  (setf *cancel_template* (make-instance 'button
					 :text "Exit"
					 :command #'(lambda () (destroy *lotloadwin*))
					 :master lotpane))
  (grid *cancel_template*  10 2  :sticky "ne" :pady 5 :padx 5) ;;don't put in column 3...



  ;;second listbox
  (setf lot2listbox (make-instance 'scrolled-listbox :master lotpane ))
  (grid lot2listbox 0 2  :sticky "nsew")
  ;;  (listbox-configure (listbox lot2listbox) :width 500) ;;doesn't work
  ;; (configure lot2listbox :width 800) ;;this won't work. 
  ;; (configure (listbox lot2listbox) :width 700) ;; this works.
  ;;year
  (setf entrybox_year (make-instance 'entry :master lotpane))
  (grid (make-instance 'label :text "Year:" :master lotpane) 2 0 :sticky "e")
  (grid entrybox_year 2 1 :pady 5)
  ;;set to current year
  (setf (text entrybox_year) (multiple-value-bind (second minute hour day month year) (get-decoded-time)(format nil "~2,'0d" year)))
  ;;month (optional)
  (setf entrybox_month (make-instance 'entry :master lotpane))
  (grid (make-instance 'label :text "Month:" :master lotpane) 3 0 :sticky "e")
  (grid entrybox_month 3 1 :pady 5)
  (setf (text entrybox_month) (multiple-value-bind (second minute hour day month year) (get-decoded-time)(format nil "~2,'0d" month)))

  (grid (make-instance 'label :text "(Leave blank for whole year.)" :master lotpane) 3 2 :sticky "w")
  
  ;;load button (2nd listbox)
  (setf *loadlot* (make-instance 'button
  				 :text "Load Lot#"
  				 :command #'(lambda () (load_lot_into_main_page))
  				 :master lotpane))
  (grid *loadlot*  0 3  :sticky "ne" :pady 5 :padx 5)
  )


